package dataHandle;

/*
 * TPCW_Populate.java - database population program
 *------------------------------------------------------------------------
 *
 * This is part of the the Java TPC-W distribution,
 * written by Harold Cain, Tim Heil, Milo Martin, Eric Weglarz, and Todd
 * Bezenek.  University of Wisconsin - Madison, Computer Sciences
 * Dept. and Dept. of Electrical and Computer Engineering, as a part of
 * Prof. Mikko Lipasti's Fall 1999 ECE 902 course.
 *
 * Copyright (C) 1999, 2000 by Harold Cain, Timothy Heil, Milo Martin, 
 *                             Eric Weglarz, Todd Bezenek.
 *
 * This source code is distributed "as is" in the hope that it will be
 * useful.  It comes with no warranty, and no author or distributor
 * accepts any responsibility for the consequences of its use.
 *
 * Everyone is granted permission to copy, modify and redistribute
 * this code under the following conditions:
 *
 * This code is distributed for non-commercial use only.
 * Please contact the maintainer for restrictions applying to 
 * commercial use of these tools.
 *
 * Permission is granted to anyone to make or distribute copies
 * of this code, either as received or modified, in any
 * medium, provided that all copyright notices, permission and
 * nonwarranty notices are preserved, and that the distributor
 * grants the recipient permission for further redistribution as
 * permitted by this document.
 *
 * Permission is granted to distribute this code in compiled
 * or executable form under the same conditions that apply for
 * source code, provided that either:
 *
 * A. it is accompanied by the corresponding machine-readable
 *    source code,
 * B. it is accompanied by a written offer, with no time limit,
 *    to give anyone a machine-readable copy of the corresponding
 *    source code in return for reimbursement of the cost of
 *    distribution.  This written offer must permit verbatim
 *    duplication by anyone, or
 * C. it is distributed by someone who received only the
 *    executable form, and is accompanied by a copy of the
 *    written offer of source code that they received concurrently.
 *
 * In other words, you are welcome to use, share and improve this codes.
 * You are forbidden to forbid anyone else to use, share and improve what
 * you give them.
 *
 ************************************************************************/


//CAVEAT:
//These TPCW DB Population routines stray from the TPCW Spec in the 
//following ways:
//1. The a_lname field in the AUTHOR table is not generated using the DBGEN
//   utility, because of the current unavailability of this utility.
//2. Ditto for the I_TITLE field of the ITEM table.

import java.io.*;
import java.sql.*;
import java.util.*;
import java.lang.Math.*;

class TPCW_Handle {
    
    private static Connection con;
    private static Random rand;
    
    //These variables are dependent on the JDBC database driver used.
    private static final String driverName = "com.ibm.db2.jcc.DB2Driver";
    private static final String dbName = "jdbc:db2://133.133.133.112:50000/TPCW";;
    
    //ATTENTION: The NUM_EBS and NUM_ITEMS variables are the only variables
    //that should be modified in order to rescale the DB.
 

    //    private static final int NUM_ADDRESSES = 10;
    public static void main(String[] args){
	System.out.println("Beginning TPCW Database population.");
	rand = new Random();
	getConnection();
	
	//Need to debug
	getSequenceDetail();
	currentOrderCount();
	searchSessionNumber(1773979);
	
	
	System.out.println("Done");
	closeConnection();
    }

    private static void searchSessionNumber(int id){
    	System.out.println("Handle the Session num");
    
   
    	try {
    	    PreparedStatement statement = con.prepareStatement
    	    ("SELECT DISTINCT(O_C_ID) from ORDERS WHERE O_ID>?");
    	
    		
    		// Set parameter
    		statement.setInt(1, id);
    	
    		  ResultSet rs = statement.executeQuery();
    		    
    		    // Results
    		    int count=0;;
    		    while(rs.next())
    		    	{
    	//	    	System.out.println(rs.getString(1));
    		    	count++;
    		    	}
    		    System.out.println("User Session number is "+ count);
    		    rs.close();
    		    statement.close();
    	
    	    con.commit();
    	}
    	catch (java.lang.Exception ex) {
    	System.err.println("Unable to get Session Num");
    	ex.printStackTrace();
    	System.exit(1);
    	}
    	System.out.print("\n");
        }
        
    
    private static void currentOrderCount(){
    	System.out.println("get current order count");
    
   
    	try {
    	    PreparedStatement statement = con.prepareStatement
    	    ("SELECT COUNT(*) from ORDERS ");
    	
    		
    		// Set parameter
    	
    	
    		  ResultSet rs = statement.executeQuery();
    		    
    		    // Results
    		  rs.next();
    		    System.out.println("Current order num is "+ rs.getInt(1));
    		    rs.close();
    		    statement.close();
    	
    	    con.commit();
    	}
    	catch (java.lang.Exception ex) {
    	System.err.println("Unable to get current Order Num");
    	ex.printStackTrace();
    	System.exit(1);
    	}
    	System.out.print("\n");
        }
    
    private static void getSequenceDetail(){
    	System.out.println("get Sequence table details");
    
   
    	try {
    	    PreparedStatement statement = con.prepareStatement
    	    ("SELECT * from SEQUENCE ");
    	
    		
    		// Set parameter
    	
    	
    		  ResultSet rs = statement.executeQuery();
    		    
    		    // Results
    		  rs.next();
    		  System.out.println("Address table now have "+ rs.getString(2)+"'s items");
    		  rs.next();
    		  System.out.println("Customer table now have "+ rs.getString(2)+"'s items");
    		  rs.next();
    		  System.out.println("Shopping card table now have "+ rs.getString(2)+"'s items");
    		  rs.next();
    		  System.out.println("Order table now have "+ rs.getString(2)+"'s items");
    		    
    		    rs.close();
    		    statement.close();
    	
    	    con.commit();
    	}
    	catch (java.lang.Exception ex) {
    	System.err.println("Unable to get sequence table");
    	ex.printStackTrace();
    	System.exit(1);
    	}
    	System.out.print("\n");
        }
    private static void getConnection(){
	try {
	    Class.forName(driverName);
	    con = DriverManager.getConnection(dbName,"db2admin","db2admin");
	    con.setAutoCommit(false);
	}
	catch (java.lang.Exception ex) {
	    ex.printStackTrace();
	}
    }  
    private static void closeConnection(){
	try {
	    con.close();
	} catch (java.lang.Exception ex) {
	    ex.printStackTrace();
	}
    }
}

